#!/usr/bin/env python
# -*- coding: UTF-8 -*-
"""
@Project : SIM7600CE-REST
@File    : sms_database.py
@Author  : Mr.Cai
@Date    : 2024/01/24 21:00
"""
import sqlite3
from sqlite3 import Error

class SmsDatabase:
    def __init__(self, db_file):
        """ 初始化函数，用于创建数据库连接和初始化数据库表 """
        self.conn = None
        try:
            self.conn = sqlite3.connect(db_file)
        except Error as e:
            print(e)

        if self.conn:
            self.create_table()

    def create_table(self):
        """ 初始化数据库表的方法 """
        table_sql = """ CREATE TABLE IF NOT EXISTS sms (
                            id integer PRIMARY KEY,
                            type text,
                            category text,
                            content text,
                            encrypt text,
                            recieve text,
                            fromnumber text,
                            tonumber text
                        ); """

        try:
            cur = self.conn.cursor()
            cur.execute(table_sql)
        except Error as e:
            print(e)
        
    def is_sms_exist(self, fromnumber, recieve, content):
        """ 通过fromnumber, recieve, content进行完全匹配，判断是否已经存在的数据 """
        cur = self.conn.cursor()
        cur.execute("SELECT count(*) FROM sms WHERE fromnumber = ? AND recieve = ? AND content = ?", (fromnumber, recieve, content))
        result = cur.fetchone()
        
        if result[0] > 0:
            return True
        return False

    def insert_sms(self, sms):
        """ 将短信数据插入数据库 """
        sql = ''' INSERT INTO sms(type, category, content, encrypt, recieve, fromnumber, tonumber)
                  VALUES(?,?,?,?,?,?,?) '''
        cur = self.conn.cursor()
        cur.execute(sql, sms)
        self.conn.commit()
        return cur.lastrowid
        
    def delete_sms(self, id):
        """ 从数据库中删除一条短信数据 """
        sql = 'DELETE FROM sms WHERE id=?'
        cur = self.conn.cursor()
        cur.execute(sql, (id,))
        self.conn.commit()

    def update_sms(self, sms):
        """ 对已有的短信数据进行更新 """
        sql = ''' UPDATE sms
                  SET type = ? ,
                      category = ? ,
                      content = ? ,
                      encrypt = ? ,
                      recieve = ? ,
                      fromnumber = ? ,
                      tonumber = ? 
                  WHERE id = ?'''
        cur = self.conn.cursor()
        cur.execute(sql, sms)
        self.conn.commit()

    def select_sms(self, id=None):
        """ 选取数据库中的一条或者全部短信数据 """
        cur = self.conn.cursor()
        if id:
            cur.execute("SELECT * FROM sms WHERE id=?", (id,))
            return cur.fetchone()
        else:
            cur.execute("SELECT * FROM sms")
            return cur.fetchall()
        
    def select_sms_by_content(self, content=''):
        """ 通过短信内容进行模糊搜索 """
        cur = self.conn.cursor()
        cur.execute("SELECT * FROM sms WHERE content LIKE ?", ('%'+content+'%',))
        return cur.fetchall()